﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Data;
[assembly: CLSCompliant(true)]
namespace University.Workshop.DataAccess
{
    public static class DataAccessProvider
    {
    
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=Pharmacy;Integrated Security=true";
        public static Provider GetProviderByNit(string nit)
        {
            string sqlSentence = "SELECT * FROM [Provider] Where Nit= @nit";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            Provider prov = new Provider(); ;
            DataTable table = new DataTable();
                try
                {
                    table.Locale= System.Globalization.CultureInfo.CurrentCulture;
                    tempCnn = new SqlConnection(ConnString);
                    cnn = tempCnn;
                    cnn.Open();
                    SqlCommand cmd = tempCnn.CreateCommand();
                    cmd.CommandText = sqlSentence;
                    cmd.Parameters.AddWithValue("@nit", nit);
                    SqlDataReader reader = cmd.ExecuteReader();
                    table.Load(reader);
                    if (table.Rows.Count != 0)
                    {
                        prov = new Provider() { Address = table.Rows[0]["Address"].ToString(), Cellular = table.Rows[0]["Cellular"].ToString(), Contact = table.Rows[0]["Contact"].ToString(), Email = table.Rows[0]["Email"].ToString(), Name = table.Rows[0]["Name"].ToString(), Nit = nit, Telephone = table.Rows[0]["Telephone"].ToString(), Web = table.Rows[0]["Web"].ToString() };

                    }
                }
                finally
                {
                    if (table != null) {
                        table.Dispose();
                    }
                    if (tempCnn != null)
                        tempCnn.Close();
                }
                return prov;
            }
        
        public static bool ProviderRegister(Provider newProvider)
        {
            string sqlSentence = "INSERT INTO [Provider] VALUES(@nit,@name,@email,@web,@contact,@cellular,@telephone,@address,@active,@date)";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            if (newProvider == null) {
                throw new BusinessException("Se quiso ingresar un cliente nulo");
            }

                try
                {
                    tempCnn = new SqlConnection(ConnString);
                    cnn = tempCnn;
                    cnn.Open();
                    SqlCommand cmd = tempCnn.CreateCommand();
                    cmd.CommandText = sqlSentence;


                    cmd.Parameters.AddWithValue("@nit", newProvider.Nit);
                    cmd.Parameters.AddWithValue("@name", newProvider.Name);
                    cmd.Parameters.AddWithValue("@email", newProvider.Email);
                    cmd.Parameters.AddWithValue("@web", newProvider.Web);
                    cmd.Parameters.AddWithValue("@contact", newProvider.Contact);
                    cmd.Parameters.AddWithValue("@cellular", newProvider.Cellular);
                    cmd.Parameters.AddWithValue("@telephone", newProvider.Telephone);
                    cmd.Parameters.AddWithValue("@address", newProvider.Address);
                    cmd.Parameters.AddWithValue("@active", 1);
                    cmd.Parameters.AddWithValue("@date", System.DateTime.Now);
                    cmd.ExecuteNonQuery();
                    return true;

                }
                finally
                {
                    if (tempCnn != null)
                        tempCnn.Close();
                }
            
        }
        public static DataTable SearchProvider(string value, string active)
        {
            string sqlSentence="Select Nit As Codigo, Name As Nombre, Contact As 'Nombre del Contacto', Cellular As Celular, Telephone As Telefono FROM Provider Where  (Nit like @value OR Name like @value OR Email like @value OR Web like @value OR Contact like @value OR Cellular like @value OR Telephone like @value OR Address like @value OR DateRegister like @value) AND (Active = @active )";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@value", "%" + value + "%");
                cmd.Parameters.AddWithValue("@active", active  );
                                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
            }
            finally
            {

                if (tempCnn != null)
                    tempCnn.Close();

                if (table != null)
                    table.Dispose();
            }
                return table;
        }
        public static bool UpdateProvider(Provider provider) {
            string sqlSentence = "UPDATE [Provider]  SET [Name] = @name ,[Email] = @email,[Web] = @Web,[Contact] = @Contact,[Cellular] = @Cellular, [Telephone] = @telephone,[Address] = @address WHERE [NIT]=@nit ";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            if (provider == null)
            {
                throw new BusinessException("Se quiso ingresar un cliente nulo");
            }
                try
                {
                    tempCnn = new SqlConnection(ConnString);
                    cnn = tempCnn;
                    cnn.Open();
                    SqlCommand cmd = tempCnn.CreateCommand();
                    cmd.CommandText = sqlSentence;

                    cmd.Parameters.AddWithValue("@name",provider.Name);
                    cmd.Parameters.AddWithValue("@email",provider.Email);
                    cmd.Parameters.AddWithValue("@Web",provider.Web);
                    cmd.Parameters.AddWithValue("@Contact",provider.Contact);
                    cmd.Parameters.AddWithValue("@Cellular",provider.Cellular);
                    cmd.Parameters.AddWithValue("@telephone",provider.Telephone);
                    cmd.Parameters.AddWithValue("@address",provider.Address);
                    cmd.Parameters.AddWithValue("@nit", provider.Nit);
                   int ra=cmd.ExecuteNonQuery();
                   return (ra!=0);
                   
                }
                finally
                {
                    if (tempCnn != null)
                        tempCnn.Close();

                }
            
        }
        public static bool ProviderDelete(string nit)
        {
            string sqlSentence = "UPDATE [Provider]  SET [Active] = 0 WHERE [NIT]=@nit";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;


                cmd.Parameters.AddWithValue("@nit", nit);
                cmd.ExecuteNonQuery();
                return true;

            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }

        }

        public static void EnableProviderData(string nitProvider)
        {
            string sqlSentence = "UPDATE [Provider]  SET [Active] = 1 WHERE [NIT]=@nit";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;


                cmd.Parameters.AddWithValue("@nit", nitProvider);
                cmd.ExecuteNonQuery();
               

            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
    }
}
